﻿using Admin.NET.Application;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Excel;
using Magicodes.ExporterAndImporter.Excel.Utility;
using OfficeOpenXml;
using OfficeOpenXml.DataValidation;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using OfficeOpenXml.Style;
using System;
using System.Data;
using System.Reflection;

namespace Admin.NET.Core.MagicodesExtend;


public class CommonExcelImporter : ExcelImporter
{
    private readonly SqlSugarRepository<CustomerChannel> _customerChannelRep;
    public CommonExcelImporter(SqlSugarRepository<CustomerChannel> customerChannelRep)
    {
        _customerChannelRep = customerChannelRep;
    }

    public override async Task<byte[]> GenerateTemplateBytes<T>() where T : class
    {
        if (typeof(T) == typeof(CustomerImportDto))
        {
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Sheet1");

       
                worksheet.Cells[1, 1].Value = "客户名称";
                worksheet.Cells[1, 2].Value = "客户类型";
                worksheet.Cells[1, 3].Value = "数量";
                worksheet.Cells[1, 4].Value = "点数";
                worksheet.Cells[1, 5].Value = "资产额（万）";
                worksheet.Cells[1, 6].Value = "是否出报告";
                worksheet.Cells[1, 7].Value = "是否收款";
                worksheet.Cells[1, 8].Value = "出报告日期";
                worksheet.Cells[1, 9].Value = "收款日期";
                worksheet.Cells[1, 10].Value = "备注";
                worksheet.Cells[1, 11].Value = "凭证备注";
                worksheet.Cells[1, 12].Value = "渠道对接";
                worksheet.Cells[1, 13].Value = "渠道名称";


                worksheet.Column(1).Width = 20;
                worksheet.Column(2).Width = 13;
                worksheet.Column(4).Width = 15;
                worksheet.Column(5).Width = 15;
                worksheet.Column(6).Width = 15;
                worksheet.Column(7).Width = 15;
                worksheet.Column(8).Width = 15;
                worksheet.Column(9).Width = 15;
                worksheet.Column(10).Width = 25;
                worksheet.Column(11).Width = 15;
                worksheet.Column(12).Width = 15;
                worksheet.Column(13).Width = 15;

                // 设置第一行（表头行）的高度
                worksheet.Row(1).Height = 20;

                // 设置第一行的背景颜色
                using (var range = worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
                {
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(SixLabors.ImageSharp.Color.Green);
                    // 设置字体加粗等样式使表头更美观
                    range.Style.Font.Bold = true;
                    range.Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.White);

                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }

                worksheet.Cells[1, 1].Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.Red);
                worksheet.Cells[1, 2].Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.Red);
                worksheet.Cells[1, 3].Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.Red);


                worksheet.Cells.LoadFromCollection(new List<CustomerImportDto>(), false);

                var channelData = _customerChannelRep.GetList(x=>x.IsDelete == false).ToList().Select(x => x.Name);

                // 设置渠道对接下拉列表
                var validation = worksheet.DataValidations.AddListValidation("L2:L1000");
                foreach (var row in channelData)
                {
                    validation.Formula.Values.Add(row.ToString());
                }

                // 设置客户类型下拉列表
                var customerTypeList = Enum.GetValues(typeof(CustomerTypeEnum))
                   .Cast<CustomerTypeEnum>()
                   .Select(e =>
                   {
                       var fieldInfo = typeof(CustomerTypeEnum).GetField(e.ToString());
                       var descriptionAttribute = fieldInfo.GetCustomAttribute<DescriptionAttribute>();
                       return descriptionAttribute?.Description ?? e.ToString();
                   })
                   .ToList();
                var customerTypeValidation = worksheet.DataValidations.AddListValidation("B2:B1000");
                foreach (var type in customerTypeList)
                {
                    customerTypeValidation.Formula.Values.Add(type);
                }

                // 设置“是否出报告” "是否收款" 下拉列表
                var options = new List<string> { "是", "否" };
                var isReportValidation = worksheet.DataValidations.AddListValidation($"F2:F1000");

                foreach (var option in options)
                {
                    isReportValidation.Formula.Values.Add(option);
                }


                // 设置“是否收款”下拉列表
                var isCollectedOptions = new List<string> { "是", "否" };
                var isCollectedValidation = worksheet.DataValidations.AddListValidation($"G2:G1000");
                foreach (var option in isCollectedOptions)
                {
                    isCollectedValidation.Formula.Values.Add(option);
                }


                var stream = new MemoryStream();
                package.SaveAs(stream);
                return stream.ToArray();
            }
        }
        //else if (typeof(T) == typeof(CustomerImportDto))
        //{
        //    //...
        //}
        else
        {
            return await base.GenerateTemplateBytes<T>();
        }
    }

}

